package com.queryBuilder.action;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.queryBuilder.dao.QueryBuilderDao;
import com.queryBuilder.entity.QueryBuilder;

@Controller
@RequestMapping("/builder")
public class QueryBuilderController {

	private QueryBuilder queryBuilder;
	private String name = "guopengfei";
	@Resource
	private QueryBuilderDao queryBuilderDao;
	@Resource
	private HibernateTemplate hibernateTemplate; 
	@PostConstruct
	public void init() {
		System.out.println("调用了init方法");
		System.out.println(queryBuilderDao);
		System.out.println(hibernateTemplate);
		List<QueryBuilder> list = queryBuilderDao.findAll(QueryBuilder.class);
		if(list !=null && list.size()>0){
			queryBuilder = list.get(0);
		}
		System.out.println(list);
		
	}
	@RequestMapping("/queryBuilder.do")
	@ResponseBody
	public String testValue(@RequestParam("selectedData") String selectedData,
			@RequestParam("val2") String val2,
			@RequestParam("dataType") String dataType,
			@RequestParam("currentOpr") String currentOpr) {
		JSONObject jsonObject = new JSONObject();
		String[] clsAndAlias = val2.split("\\.");
		try {
			jsonObject.put("dataType", dataType);
			jsonObject.put("values",
					getAllValues(clsAndAlias[0], clsAndAlias[1]));
			jsonObject.put("currentOpr", currentOpr);
			jsonObject.put("selecVal", new JSONArray());
		} catch (JSONException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		System.out.println("从数据库中获取到的数据为：\n" + jsonObject.toString());
		return jsonObject.toString();
	}

	/**
	 * 根据传入的别名和列名获取数据库中对应的所有的值
	 * 
	 * @param aliaName
	 *            别名
	 * @param proName
	 *            列名
	 * @return 所有的值组成的JSONArray对象
	 * @throws JSONException
	 *             抛出异常
	 */
	private JSONArray getAllValues(String aliaName, String proName)
			throws JSONException {
		JSONArray allValues = new JSONArray();
		String clsAndAliaObj = queryBuilder.getClassalias();
		String className = null;
		JSONArray jsonArray = new JSONArray(clsAndAliaObj);
		allClsAndAlis: for (int i = 0; i < jsonArray.length(); i++) {
			JSONObject jsonObject = jsonArray.getJSONObject(i);
			JSONArray jsonArray2 = jsonObject.getJSONArray("alias");
			for (int j = 0; j < jsonArray2.length(); j++) {
				String tempAlia = jsonArray2.getString(j);
				if (tempAlia.trim().equals(aliaName)) {
					className = jsonObject.getString("className");
					break allClsAndAlis;
				}
			}
		}

		if (className != null && !className.trim().equals("")) {
			allValues = queryBuilderDao.findByClsAndPro(className, proName);
		}
		return allValues;
	}
	/**
	 * 接收前段查询条件建立的查询语句
	 * @param sqlWhere     wherSQL
	 * @param condition    前端页面的查询条件的结构，用于下次回显
	 * @return
	 */
	@RequestMapping("/loadSQL.do")
	@ResponseBody
	public String getSQL(@RequestParam("sqlWhere") String sqlWhere,
			@RequestParam("condition") String condition, Model model){
		JSONObject jsonObject = new JSONObject();
		JSONArray jsonArray = new JSONArray();
		try {
			List<Object[]> result = getQueryResultData(sqlWhere);
			jsonArray = listToJson(result);
			jsonObject.put("colums",mapToJson(getResultColus()) );
			jsonObject.put("values", jsonArray);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(jsonObject.toString());
		return jsonObject.toString();
	}
	/**
	 * 获取查询结果
	 * @param whereSQL  传入wheresql语句
	 * @return  List<Object []>结果集
	 * @throws JSONException 
	 */
	private  List<Object[]>  getQueryResultData(String whereSQL) throws JSONException{
		StringBuffer stringBuffer = new StringBuffer();
		Map<String, String> map = getResultColus();
		Iterator<String> iterator = map.keySet().iterator();
		while (iterator.hasNext()) {
			String colum = iterator.next();
			stringBuffer.append(colum).append(",");
		}
		String selecSQL = "select "+stringBuffer.substring(0,stringBuffer.length()-1)+" ";
		String fromSQL = "from User as user1 ";
		List<Object []> list = hibernateTemplate.find(selecSQL+fromSQL+" where "+whereSQL);
		return list;
	}
	
	/**
	 * 将list转为JsonArray
	 * @param list
	 * @return
	 * @throws JSONException
	 */
	private JSONArray listToJson(List<Object[]> list) throws JSONException{
		JSONArray jsonArray = new JSONArray() ;
		for(Object [] objects :list){
			JSONObject jsonObject = new JSONObject();
			JSONArray jsonArray2 = new JSONArray();
			for(Object object :objects){
				jsonArray2.put(object.toString());
			}
			jsonObject.put("values", jsonArray2);
			jsonArray.put(jsonObject);
		}
		return jsonArray;
	}
	
	/**
	 * 将map转为json
	 * @param map   格式为map<Object,Object>
	 * @return   jsonArray
	 */
	private JSONArray mapToJson(Map<String, String> map){
		Iterator<String> iterator = map.keySet().iterator();
		JSONArray jsonArray = new JSONArray();
		while (iterator.hasNext()) {
			jsonArray.put(iterator.next());
		}
		return jsonArray;
	}
	
	/**
	 * 获取所查询模板的列和在结果中显示的列的label
	 * @return map集合  key为结果列，value为列对应的label
	 * @throws JSONException 
	 */
	public Map<String, String> getResultColus () throws JSONException{
		String tempString = queryBuilder.getDiaplaiedPro();	
		Map<String, String> map = new HashMap<String, String>();
		JSONArray jsonArray = new JSONArray(tempString);
		for(int i = 0 ;i<jsonArray.length();i++){
			JSONObject jsonObject = jsonArray.getJSONObject(i);
//			[{"alia":"user1","pros":["id","name"]}]
			String alia = jsonObject.getString("alia");
			JSONArray jsonArray2 = jsonObject.getJSONArray("pros");
			for(int j = 0 ;j<jsonArray2.length();j++){
				String colum = jsonArray2.getString(j);
				map.put(colum, null);
			}
		}
		return map;
	}
	public QueryBuilder getQueryBuilder() {
		return queryBuilder;
	}

	public void setQueryBuilder(QueryBuilder queryBuilder) {
		this.queryBuilder = queryBuilder;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}

}
